#!/bin/bash
#声明当前脚本使用bash解释器质性
#接收外部传入的日期参数，如果外部没有传入日期参数，怎么办？
#严格:外部没有传入，就退出 exit  非严格：如果外部没有传入，自己生成一个默认的日期(当前机器时间的昨天)
#-n代表判断字符串的长度，如果字符串长度为0，返回false,否则返回true
if [ -n "$1" ]
then
	do_date=$1
else
	do_date= $(date -d yesterday +%F)
fi

echo 当前要操作的日期是$do_date

dwd_trade_pay_detail_suc_inc="
    with oi as (
    select
           data.user_id,
           data.province_id,
           data.id
    from ods_order_info_inc
    where dt = $do_date
),
     od as (
         select
                data.id,
                data.order_id,
                data.course_id,
                data.course_name course_name,
                data.origin_amount,
                data.coupon_reduce,
                data.final_amount
         from ods_order_detail_inc
         where (dt = date_sub($do_date,1) or dt = '2020-07-05')
           and (type = 'bootstrap-insert' or type='insert')
     ),
     py as (
         select
                data.id,
                data.order_id,
                data.payment_type,
                data.payment_status,
                data.callback_time
         from ods_payment_info_inc
         where dt = $do_date  and type='update'
            and array_contains(map_keys(old),'callback_time') and data.callback_time is not null
     )
insert overwrite table dwd_trade_pay_detail_suc_inc partition(dt = $do_date)
select
       py.id id,
       oi.id order_id,
       oi.user_id user_id,
       od.course_id course_id,
       od.course_name course_name,
       oi.province_id province_id,
       py.payment_type payment_type,
       py.payment_status payment_status,
       date_format(py.callback_time, 'yyyy-MM-dd') date_id,
       py.callback_time callback_time,
       od.origin_amount origin_amount,
       od.coupon_reduce coupon_reduce,
       od.final_amount final_amount
from py
         left join od
                   on py.order_id=od.order_id
         left join oi
                   on py.order_id = oi.id;
"
dwd_traffic_action_inc="
    set hive.cbo.enable=false;
insert overwrite table dwd_traffic_action_inc partition(dt=$do_date)
select
    province_id,     --'
    brand,          --'手机品牌'
    channel,         --'渠道'
    is_new,          --'是否首次启动'
    model,          --'手机型号'
    mid_id,          --'设备id'
    operate_system,  --'操作系统'
    sc,              --来源
    sid,            --会话id
    user_id,         --'会员id'
    version_code,    --'app版本号'
    during_time,     --'持续时间毫秒'
    page_item,      --'目标id '
    page_item_type,  --'目标类型'
    last_page_id,    --'上页类型'
    page_id,         --'页面id '
    action_id,       --'动作id'
    action_item,     --'目标id '
    action_item_type,    --'目标类型'
    date_format(from_utc_timestamp(ts,'Asia/Shanghai'),'yyyy-MM-dd') date_id,         --'日期id'
    date_format(from_utc_timestamp(ts,'Asia/Shanghai'),'yyyy-MM-dd HH:mm:ss') action_time     --'动作发生时间'
	from(

		select
			common.ar province_id,  --
			common.ba brand,        --
			common.ch channel,
			common.is_new is_new,
			common.md model,
			common.mid mid_id,
			common.os operate_system,
		    common.sc ,
		    common.sid ,
			common.uid user_id,
			common.vc version_code,
			page.during_time,
			page.item page_item,
			page.item_type page_item_type,
			page.last_page_id,
			page.page_id,
			action.action_id,
			action.item action_item,
			action.item_type action_item_type,
			action.ts
		from ods_log_inc lateral view explode(actions) tmp as action
		where dt = $do_date and actions is not null
	)log;
"
dwd_paly_video_inc="
    with log as (
    select
			common.uid ,
           common.sid,
           appvideo.play_sec,
           appvideo.position_sec,
           appvideo.video_id,
           date_format(from_utc_timestamp(ts,'Asia/Shanghai'),'yyyy-MM-dd') play_time
    from ods_log_inc
    where dt=$do_date and appvideo.play_sec is not null
    ),ucp as (
        select
            data.id,
               data.course_id,
               data.chapter_id,
               data.user_id,
               data.position_sec,
               data.create_time
        from ods_user_chapter_process_inc
        where dt = $do_date
)
insert overwrite  table dwd_paly_video_inc partition (dt=$do_date)
select
    log.uid user_id,
       log.play_sec,
       log.position_sec,
       log.video_id,
       log.sid,
       log.play_time,
       ucp.course_id,
       ucp.chapter_id
from log left join ucp
    on log.uid=ucp.user_id;
"
dwd_paly_course_inc="
    with vi as (
    select
        id,
           video_name,
           during_sec,
           video_status,
           version_id,
           chapter_id,
           course_id
    from ods_video_info_full
    where dt=$do_date and  video_status = '1'
),ucp as (
    select
        data.id,
           data.course_id,
           data.chapter_id,
           data.user_id,
           data.position_sec,
           date_format(data.create_time,'yyyy-MM-dd') create_time
    from ods_user_chapter_process_inc
    where dt=$do_date and type = 'bootstrap-insert'
),log as (
    select
			common.uid ,
           common.sid,
           sum(appvideo.play_sec) play_sec,
           appvideo.video_id
    from ods_log_inc
    where dt=$do_date and appvideo.play_sec is not null
    group by common.uid, common.sid,appvideo.video_id

)
insert overwrite  table dwd_paly_course_inc partition(dt=$do_date)
select
    ucp.id id,
       ucp.course_id,
       ucp.chapter_id,
       ucp.user_id,
       ucp.create_time,
       ucp.position_sec,
       vi.during_sec,
       log.play_sec,
       log.sid,
       vi.id video_id,
       vi.video_name,
       vi.video_status,
       vi.version_id

from ucp left join vi
    on ucp.course_id=vi.course_id
    and ucp.chapter_id=vi.chapter_id
left join log
    on ucp.user_id=log.uid
    and vi.id =log.video_id;
"
dwd_user_login_inc="
insert overwrite table dwd_user_login_inc partition (dt='2022-07-05')
select
        user_id,
        date_id,
        view_time login_time,
        channel,
        province_id,
        version_code,
        mid_id,
        brand,
        model,
        operate_system
from (
         select user_id,
                date_id,
                channel,
                province_id,
                version_code,
                mid_id,
                brand,
                model,
                operate_system,
                view_time,
                -- 每个会话中第一次
                row_number() over (partition by session_id order by view_time ) rn
         from dwd_traffic_page_view_inc
         where dt = '2022-07-05'
           -- 过滤得到所有 userid 不为null的页面访问
           and isnotnull(user_id)
     ) t1
where rn = 1;
"
dwd_trade_cart_add_inc="
set hive.strict.checks.type.safety=false;
insert overwrite table dwd_trade_cart_add_inc partition (dt='$do_date')
select
    data.id,
    data.user_id,
    data.course_id,
    data.course_name,
    date_format(from_utc_timestamp(ts*1000,'Asia/Shanghai') ,'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts*1000,'Asia/Shanghai') ,'yyyy-MM-dd HH:mm:ss') create_time,
    data.session_id
from gmall.ods_cart_info_inc
where dt='$do_date' and (type='insert' or (type='update' and old['update_time'] is not null ) );
"
dwd_traffic_display_inc="
with td as (
    select
        common.ar,
        common.ba,
        common.ch,
        common.is_new,
        common.md,
        common.mid,
        common.os,
        common.uid,
        common.vc,
        page.during_time,
        page.item,
        page.item_type,
        page.last_page_id,
        page.page_id,
        common.sc source_id,
        display.display_type,
        display.item display_item,
        display.item_type display_item_type,
           -- order 会和 sql中的order冲突
        display.`order` display_order,
        display.pos_id,
           ts
    from ods_log_inc
    lateral view explode(displays) tmp as display
    where dt='$do_date'
      -- 过滤曝光数据
    and size(displays) > 0
),bp as (
   -- 获取省份信息
    select
            id province_id,
           -- area_code 和 省份id是 1:1
           area_code,
        province_name
    -- 维度信息要从dim维度表取
    from dim_province_full
    where dt='$do_date'
),sc as (
    -- 获取来源类型
    select
            id source_id,
           source_site source_type
    from ods_base_source_full
    where dt='$do_date'
)
insert overwrite table dwd_traffic_display_inc partition (dt='$do_date')
select
       area_code,
       ba brand,
       ch channel,
       is_new,
       md model,
       mid mid_id,
       os operate_system,
       uid user_id,
       vc version_code,
       during_time,
      item page_item,
      item_type page_item_type,
       last_page_id,
       page_id,
       td.source_id,
       source_type,
       '$do_date' date_id,
      date_format(from_utc_timestamp(ts,'Asia/Shanghai'),'yyyy-MM-dd HH:mm:ss')  display_time,
       display_type,
       display_item,
       display_item_type,
       display_order,
       pos_id display_pos_id
from td
left join bp on td.ar=bp.province_id
left join sc on td.source_id=sc.source_id;
"
dwd_examine_test_exam_inc="
with exam as (
    select
        data.id exam_id,
        data.paper_id,
        data.user_id,
        data.score,
        data.duration_sec,
        data.create_time,
        data.submit_time,
        data.update_time,
        date_format(from_utc_timestamp(ts*1000,'Asia/Shanghai'),'yyyy-MM-dd HH:mm:ss') date_id
    from ods_test_exam_inc
    where dt='$do_date' and type='bootstrap-insert'
),us as (
    select
        data.id,
        data.real_name user_name
    from ods_user_info_inc
    where dt='$do_date' and type='bootstrap-insert'
),paper as (
    select
        id paper_id,
        paper_title paper_name,
        course_id
    from ods_test_paper_full
    where dt='$do_date'
),course as (
    select
        id course_id,
        course_name
    from ods_course_info_full
    where dt='$do_date'
),question as (
    select
        data.id question_id,
        data.score question_score,
        data.paper_id
    from ods_test_exam_question_inc
    where dt='$do_date' and type='bootstrap-insert'
)
insert overwrite table dwd_examine_test_exam_inc partition (dt='$do_date')
select
       exam_id,
       user_id,
       user_name,
       paper.course_id,
       course_name,
       exam.paper_id,
       paper_name,
       score,
       date_id,
       create_time,
       duration_sec,
       submit_time,
       update_time,
       question_id,
       question_score
from exam
left join us on user_id=us.id
left join paper on exam.paper_id=paper.paper_id
left join course on paper.course_id=course.course_id
join question on exam.paper_id = question.paper_id
"
case $2 in
"dwd_trade_pay_detail_suc_inc")
	hive --database gmall -e "$dwd_trade_pay_detail_suc_inc"
	;;
"dwd_traffic_action_inc")
	hive --database gmall -e "$dwd_traffic_action_inc"
	;;
"dwd_paly_video_inc")
	hive --database gmall -e "$dwd_paly_video_inc"
	;;
"dwd_paly_course_inc")
	hive --database gmall -e "$dwd_paly_course_inc"
	;;
"dwd_user_login_inc")
	hive --database gmall -e "$dwd_user_login_inc"
	;;
"dwd_trade_cart_add_inc")
	hive --database gmall -e "$dwd_trade_cart_add_inc"
	;;
"dwd_traffic_display_inc")
	hive --database gmall -e "$dwd_traffic_display_inc"
	;;
"dwd_examine_test_exam_inc")
	hive --database gmall -e "$dwd_examine_test_exam_inc"
	;;
"all")
	hive --database gmall -e "$dwd_trade_pay_detail_suc_inc$dwd_traffic_action_inc$dwd_paly_video_inc$dwd_paly_course_inc$dwd_traffic_display_inc$dwd_examine_test_exam_inc"
	;;
esac